﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Domain;

namespace DAL
{
    public class PessoaDAO
    {
        //private PessoaDAO instance = new PessoaDAO();

        /*public static PessoaDAO Instance
        {
            get { return PessoaDAO.instance; }
        }*/

        public PessoaDAO() { }

        public bool GravarPessoa(Pessoa pes)
        {
            SqlCommand cm = null;
            SqlConnection conn = null;
            bool ret = false;

            try
            {
                conn = Conexao.Instance.getConnection();
                String sql = "insert into usuario values ('" + pes.Username + "','"
                        + pes.Nome + "','" + pes.Cpf + "','" + pes.Rg + "','"
                        + pes.Telefone + "','" + pes.Celular + "','" + pes.Email + "'," + pes.Perfil + ")";

                cm = new SqlCommand(sql, conn);
                cm.ExecuteNonQuery();

                ret =  true;
            }
            catch (SqlException e) {
                ret = false;
                throw e;
            }
            finally {
                try
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                catch (SqlException e) {
                    throw e;
                }
            }
            return ret;
        }

        public bool UpdatePessoa(Pessoa pes)
        {
            SqlCommand cm = null;
            SqlConnection conn = null;
            bool ret = false;
            string sql = string.Empty;

            try
            {
                conn = Conexao.Instance.getConnection();

                sql = " update usuario set ";
                sql += " nome = '" + pes.Nome + "',";
                sql += " cpf = '" + pes.Cpf+ "',";
                sql += " rg = '" + pes.Rg + "',";
                sql += " telefone = '" + pes.Telefone + "',";
                sql += " celular = '" + pes.Celular + "',";
                sql += " perfilId = '" + pes.Perfil + "',";
                sql += " email = '" + pes.Email + "'";
                sql += " where ";
                sql += "    id = " + pes.Id;

                cm = new SqlCommand(sql, conn);
                cm.ExecuteNonQuery();

                ret = true;
            }
            catch (SqlException e)
            {
                ret = false;
                throw e;
            }
            finally
            {
                try
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
            return ret;
        }

        public bool DeletePessoa(Int32 id)
        {
            SqlCommand cm = null;
            SqlConnection conn = null;
            bool ret = false;
            string sql = string.Empty;

            try
            {
                conn = Conexao.Instance.getConnection();

                sql = " delete from usuario where id =" + id;

                cm = new SqlCommand(sql, conn);
                cm.ExecuteNonQuery();

                return true;
            }
            catch (SqlException e)
            {
                ret = false;
                throw e;
            }
            finally
            {
                try
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
            return ret;
        }

        public DataTable PessoaFiltra(string filtro)
        {
            string sql = "";

            try
            {
                sql = " select * from usuario ";
                sql += " where ";
                sql += " id = @id";
                sql += " or ";
                sql += " nome like '%' + @nome + '%' ";

                return utils.executeSql(sql, filtro);
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public DataTable PessoaList()
        {
            string sql = "";

            try
            {
                sql = " select * from usuario ";

                return utils.returnDataTable(sql);
            }
            catch (SqlException e)
            {
                throw e;
            }
        }

        public Pessoa Select(int id)
        {
            SqlCommand cm = new SqlCommand();
            SqlConnection conn = null;

            SqlDataReader dr = null;

            try
            {
                conn = Conexao.Instance.getConnection();
                String sql = "select * from usuario where id = " + id;

                cm.Connection = conn;
                cm.CommandText = sql;

                dr = cm.ExecuteReader();

                if (!dr.Read())
                {
                    throw new Exception("Não foi encontrado nenhum " + "registro com Código: " + id);
                }
                //dr.GetString(1);
                string username = dr["username"].ToString();
                string nome = dr["nome"].ToString();
                string senha = "";
                string cpf = dr["cpf"].ToString();
                string rg = dr["rg"].ToString();
                string telefone = dr["telefone"].ToString();
                string celuar = dr["celular"].ToString();
                string email = dr["email"].ToString();
                string perfil = dr["perfilId"].ToString();

                return new Pessoa(id, username, senha, nome, cpf, rg, telefone, celuar, email, perfil);
            }
            catch (SqlException e) {
                throw e;
            }
            finally
            {
                try
                {
                    if (dr != null)
                    {
                        dr.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                catch (SqlException e) {
                    throw e;
                }
            }
        }

        public bool Loga(string username, string senha)
        {
            SqlCommand cm = null;
            SqlConnection conn = null;
            string sql = null;
            bool ret = false;

            try
            {
                username = username.ToLower();

                conn = Conexao.Instance.getConnection();

                sql = " select count(*) ";
                sql += " from ";
                sql += "    usuario ";
                sql += " where username = @username ";
                sql += " and senha = @senha ";


                cm = new SqlCommand(sql, conn);
                cm.Parameters.Add("@username", System.Data.SqlDbType.VarChar, 32);
                cm.Parameters.Add("@senha", System.Data.SqlDbType.VarChar, 20);

                cm.Parameters[0].Value = username;
                cm.Parameters[1].Value = senha;

                

                ret = (((int)cm.ExecuteScalar()) > 0);
            }
            catch (SqlException e)
            {
                ret = false;
                throw e;
            }
            finally
            {
                try
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                catch (SqlException e)
                {
                    throw e;
                }
            }
            return ret;
        }
    }
}
